﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace Dry.DbInterpreter
{
    public class DbTable : General.DryBase
    {
        private string _strTableName;
        private string _tablePrefix;
        List<DbColumn> _lstDbColumns = new List<DbColumn>();

        public string TableName
        {
            get { return _strTableName; }
            set { _strTableName = value; }
        }

        public string ObjectName
        {
            get { return TableName.Substring(_tablePrefix.Length, 1).ToLower() + TableName.Substring(4); }
        }

        public string ClassName
        {
            get { return TableName.Substring(_tablePrefix.Length); }
        }

        public List<DbColumn> DbColumns
        {
            get { return _lstDbColumns; }
        }

        public static List<DbTable> GetAllDbTables()
        {
            return GetAllDbTables("tbl");
        }

        public static List<DbTable> GetAllDbTables(string tablePrefix)
        {
            if (tablePrefix == "")
                tablePrefix = "tbl";

            List<DbTable> lstDbTables = new List<DbTable>();

            Database db = GetDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand("select name from sys.tables order by name");
            using (SqlDataReader dataReader = (SqlDataReader)db.ExecuteReader(dbCommand))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        if (dataReader["name"].ToString().StartsWith(tablePrefix))
                            lstDbTables.Add(new DbTable(dataReader["name"].ToString(), tablePrefix));
                    }
                }
            }

            return lstDbTables;
        }

        public DbTable(string strTableName, string tablePrefix)
        {
            _strTableName = strTableName;
            _tablePrefix = tablePrefix;

            Database db = GetDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(GetSql4TableInfo());
            db.AddInParameter(dbCommand, "TableName", DbType.String, _strTableName);
            using (SqlDataReader dataReader = (SqlDataReader)db.ExecuteReader(dbCommand))
            {
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        _lstDbColumns.Add(new DbColumn(dataReader, _tablePrefix));
                    }
                }
                else
                {
                    throw new Exception("Can not find a table with the name '" + _strTableName + "'");
                }
            }
        }

        private static string GetSql4TableInfo()
        {
            return @"
SELECT DISTINCT 
	c.name As ColumnName, ty.Name as [DataType], c.max_length as MaxLength, c.is_nullable as IsNullable,
	CAST(CASE WHEN i.is_primary_key = 1 THEN 1 ELSE 0 END AS BIT) as IsPK,                       
	CAST(CASE WHEN fk.parent_column_id is null THEN 0 ELSE 1 END AS BIT) AS IsFK,                       
	CAST(CASE WHEN CAST(ep.value as nvarchar) LIKE '%*SEL*%' THEN 1 ELSE 0 END AS BIT) AS IsSelectable,
	CAST(CASE WHEN CAST(ep.value as nvarchar) LIKE '%*LIKE*%' THEN 1 ELSE 0 END AS BIT) AS IsLikeble,
	CAST(CASE WHEN CAST(ep.value as nvarchar) LIKE '%*FROM*%' THEN 1 ELSE 0 END AS BIT) AS [IsFrom],
	CAST(CASE WHEN CAST(ep.value as nvarchar) LIKE '%*TO*%' THEN 1 ELSE 0 END AS BIT) AS [IsTo],
	OBJECT_NAME(fk.referenced_object_id) AS ReferenceTable
FROM       
	sys.objects o inner join 
	sys.columns c on o.object_id=c.object_id inner join
	sys.tables t on o.object_id=t.object_id inner join 
	sys.types ty on c.user_type_id=ty.user_type_id LEFT OUTER JOIN  
	sys.extended_properties ep on ep.major_id = c.object_id AND ep.minor_id = c.column_id LEFT OUTER JOIN 
	sys.foreign_key_columns fk on fk.parent_object_id=o.object_id  and fk.parent_column_id=c.column_id LEFT OUTER JOIN
	sys.index_columns ic on ic.object_id=o.object_id and ic.column_id=c.column_id LEFT OUTER JOIN
	sys.indexes i on ic.object_id=i.object_id and ic.index_column_id=c.column_id and i.is_primary_key=1
WHERE     
	t.name = @TableName
";
        }
    }
}
